package cn.px.office.tool;

import cn.px.office.annotation.Excel;
import cn.px.office.poi.cache.DictHandle;
import cn.px.office.poi.excel.ExcelExportUtil;
import cn.px.office.poi.excel.ExcelImportUtil;
import cn.px.office.poi.excel.entity.ExportParams;
import cn.px.office.poi.excel.entity.ImportParams;
import cn.px.office.poi.excel.entity.params.ExcelExportEntity;
import cn.px.office.poi.excel.export.styler.ExcelExportStylerBorderImpl;
import cn.px.office.poi.excel.export.styler.ExcelExportStylerCustomImpl;
import cn.px.office.poi.excel.export.styler.ExcelExportStylerDefaultImpl;
import cn.px.office.poi.util.DateUtils;
import cn.px.office.poi.util.PoiPublicUtil;
import cn.px.office.vo.ExcelData;
import lombok.SneakyThrows;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;

/**
 * 使用
 *
 * @author 品讯科技
 * @create 2017-12-12
 */
public class ToolPoi {

    /**
     * poi报表文件后缀
     */
    private static final String HSSF = ".xls";
    private static final String XSSF = ".xlsx";

    private static final String EXPORT_PATH = PoiPublicUtil.getWebRootPath("export" + File.separator);

    /**
     * @param response  输出对象
     * @param pojoClass Excel对象Class
     * @param dataSet   Excel对象数据List
     */
    public static <T> void exportResponse(HttpServletResponse response, Class<T> pojoClass, Collection<?> dataSet, String name) {
        ExportParams exportParams = new ExportParams();
        exportParams.setTitle(name);
        exportParams.setSheetName(name);
        exportParams.setStyle(ExcelExportStylerDefaultImpl.class);
        exportResponse(response,
                name + DateUtils.dateToString(LocalDateTime.now(), "_yyyy_MM_dd_HH_mm_ss_SSS"),
                new ExportParams(), pojoClass, dataSet);
    }

    /**
     * @param response  输出对象
     * @param fileName  文件名
     * @param title     表格标题属性
     * @param sheetName sheet属性
     * @param pojoClass Excel对象Class
     * @param dataSet   Excel对象数据List
     */
    public static void exportResponse(HttpServletResponse response, String fileName, String title, String sheetName, Class<?> pojoClass, Collection<?> dataSet) {
        ExportParams exportParams = new ExportParams();
        exportParams.setTitle(title);
        exportParams.setSheetName(sheetName);
        exportParams.setStyle(ExcelExportStylerDefaultImpl.class);
        exportResponse(response, fileName, exportParams, pojoClass, dataSet);
    }


    /**
     * @param response  输出对象
     * @param pojoClass Excel对象Class
     * @param dataSet   Excel对象数据List
     */
    public static <T> void exportResponse(HttpServletResponse response, DictHandle dictHandle, Class<T> pojoClass, Collection<?> dataSet, String name) {
        ExportParams exportParams = new ExportParams();
        exportParams.setTitle(name);
        exportParams.setSheetName(name);
        exportParams.setStyle(ExcelExportStylerBorderImpl.class);
        exportResponse(response, dictHandle,
                name + DateUtils.dateToString(LocalDateTime.now(), "yyyyMMddHHmmssSSS"),
                exportParams, pojoClass, dataSet);
    }

    /**
     * @param response   输出对象
     * @param entityList Excel对象Class
     * @param dataSet    Excel对象数据List
     */
    public static <T> void exportResponse(HttpServletResponse response, DictHandle dictHandle, List<ExcelExportEntity> entityList,
                                          Collection<? extends ExcelData> dataSet, String name) {
        ExportParams exportParams = new ExportParams();
        exportParams.setTitle(name);
        exportParams.setSheetName(name);
        exportParams.setStyle(ExcelExportStylerCustomImpl.class);
        exportResponse(response, dictHandle,
                name + DateUtils.dateToString(LocalDateTime.now(), "yyyyMMddHHmmssSSS"),
                exportParams, entityList, dataSet);
    }

    /**
     * @param response   输出对象
     * @param pojoClass Excel对象Class
     * @param dataSet    Excel对象数据List
     */
    public static <T> void exportCustomTitleResponse(HttpServletResponse response, DictHandle dictHandle, List<ExcelData> customTitleList, Class<?> pojoClass,
                                                     Collection<? extends ExcelData> dataSet, String name) {
        ExportParams exportParams = new ExportParams();
        exportParams.setTitle(name);
        exportParams.setSheetName(name);
        exportParams.setCreateHeadRows(false);
        exportParams.setStyle(ExcelExportStylerCustomImpl.class);
        exportParams.setCustomTitleList(customTitleList);
        exportCustomResponse(response, dictHandle,
                name + DateUtils.dateToString(LocalDateTime.now(), "yyyyMMddHHmmssSSS"),
                exportParams, pojoClass, dataSet);
    }

    /**
     * @param response   输出对象
     * @param pojoClass Excel对象Class
     * @param dataSet    Excel对象数据List
     */
    public static <T> void exportCustomTitleMergeRowResponse(HttpServletResponse response, DictHandle dictHandle, List<ExcelData> customTitleList, Class<?> pojoClass,
                                                     Collection<? extends ExcelData> dataSet, String name) {
        ExportParams exportParams = new ExportParams();
        exportParams.setTitle(name);
        exportParams.setSheetName(name);
        exportParams.setCreateHeadRows(false);
        exportParams.setStyle(ExcelExportStylerCustomImpl.class);
        exportParams.setCustomTitleList(customTitleList);
        exportCustomMergeRowResponse(response, dictHandle,
                name + DateUtils.dateToString(LocalDateTime.now(), "yyyyMMddHHmmssSSS"),
                exportParams, pojoClass, dataSet);
    }

    /**
     * @param response  输出对象
     * @param fileName  文件名
     * @param title     表格标题属性
     * @param sheetName sheet属性
     * @param pojoClass Excel对象Class
     * @param dataSet   Excel对象数据List
     */
    public static void exportResponse(HttpServletResponse response, DictHandle dictHandle, String fileName, String title, String sheetName, Class<?> pojoClass, Collection<?> dataSet) {
        ExportParams exportParams = new ExportParams();
        exportParams.setTitle(title);
        exportParams.setSheetName(sheetName);
        exportParams.setStyle(ExcelExportStylerDefaultImpl.class);
        exportResponse(response, dictHandle, fileName, exportParams, pojoClass, dataSet);
    }


    /**
     * @param response     输出对象
     * @param fileName     文件名
     * @param exportParams 表格标题属性
     * @param pojoClass    Excel对象Class
     * @param dataSet      Excel对象数据List
     */
    @SneakyThrows
    public static void exportResponse(HttpServletResponse response, DictHandle dictHandle, String fileName, ExportParams exportParams, Class<?> pojoClass, Collection<?> dataSet) {
        Workbook workbook = ExcelExportUtil.exportExcel(dictHandle, exportParams, pojoClass, dataSet);
        if (workbook instanceof HSSFWorkbook) {
            fileName += HSSF;
        } else {
            fileName += XSSF;
        }
        response.setCharacterEncoding("UTF-8");
        // 设置contentType为excel格式
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        // 默认Excel名称
        response.setHeader("Content-Disposition",
                "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }

    /**
     * @param response     输出对象
     * @param fileName     文件名
     * @param exportParams 表格标题属性
     * @param entityList   Excel对象Class
     * @param dataSet      Excel对象数据List
     */
    @SneakyThrows
    public static void exportResponse(HttpServletResponse response, DictHandle dictHandle, String fileName, ExportParams exportParams,
                                      List<ExcelExportEntity> entityList, Collection<? extends ExcelData> dataSet) {
        Workbook workbook = ExcelExportUtil.exportExcel(dictHandle, exportParams, entityList, dataSet);
        if (workbook instanceof HSSFWorkbook) {
            fileName += HSSF;
        } else {
            fileName += XSSF;
        }
        response.setCharacterEncoding("UTF-8");
        // 设置contentType为excel格式
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        // 默认Excel名称
        response.setHeader("Content-Disposition",
                "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }

    /**
     * @param response     输出对象
     * @param fileName     文件名
     * @param exportParams 表格标题属性
     * @param pojoClass   Excel对象Class
     * @param dataSet      Excel对象数据List
     */
    @SneakyThrows
    public static void exportCustomResponse(HttpServletResponse response, DictHandle dictHandle, String fileName, ExportParams exportParams,
                                      Class<?> pojoClass, Collection<? extends ExcelData> dataSet) {
        Workbook workbook = ExcelExportUtil.exportCustomExcel(dictHandle, exportParams, pojoClass, dataSet);
        if (workbook instanceof HSSFWorkbook) {
            fileName += HSSF;
        } else {
            fileName += XSSF;
        }
        response.setCharacterEncoding("UTF-8");
        // 设置contentType为excel格式
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        // 默认Excel名称
        response.setHeader("Content-Disposition",
                "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }

    /**
     * @param response     输出对象
     * @param fileName     文件名
     * @param exportParams 表格标题属性
     * @param pojoClass   Excel对象Class
     * @param dataSet      Excel对象数据List
     */
    @SneakyThrows
    public static void exportCustomMergeRowResponse(HttpServletResponse response, DictHandle dictHandle, String fileName, ExportParams exportParams,
                                            Class<?> pojoClass, Collection<? extends ExcelData> dataSet) {
        Workbook workbook = ExcelExportUtil.exportCustomExcelMergeRow(dictHandle, exportParams, pojoClass, dataSet);
        if (workbook instanceof HSSFWorkbook) {
            fileName += HSSF;
        } else {
            fileName += XSSF;
        }
        response.setCharacterEncoding("UTF-8");
        // 设置contentType为excel格式
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        // 默认Excel名称
        response.setHeader("Content-Disposition",
                "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }

    /**
     * @param response     输出对象
     * @param fileName     文件名
     * @param exportParams 表格标题属性
     * @param pojoClass    Excel对象Class
     * @param dataSet      Excel对象数据List
     */
    @SneakyThrows
    public static void exportResponse(HttpServletResponse response, String fileName, ExportParams exportParams, Class<?> pojoClass, Collection<?> dataSet) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, dataSet);
        if (workbook instanceof HSSFWorkbook) {
            fileName += HSSF;
        } else {
            fileName += XSSF;
        }
        response.setCharacterEncoding("UTF-8");
        // 设置contentType为excel格式
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        // 默认Excel名称
        response.setHeader("Content-Disposition",
                "attachment;filename=" + java.net.URLEncoder.encode(fileName, "UTF-8"));
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }


    /**
     * @param fileName     文件名
     * @param exportParams 表格标题属性
     * @param pojoClass    Excel对象Class
     * @param dataSet      Excel对象数据List
     */
    @SuppressWarnings("all")
    public static <T> File export(String sheetName, String title, Class<T> tClass, List<Map<String, Object>> exportValue) {
        File file = null;
        Workbook workbook = exportData(sheetName, title, tClass, exportValue);

        if (workbook instanceof HSSFWorkbook) {
            sheetName += HSSF;
        } else {
            sheetName += XSSF;
        }

        try {
            file = new File(EXPORT_PATH + sheetName);
            if (!file.exists()) {
                file.getParentFile().mkdir();
                file.createNewFile();
            }
            OutputStream fos = new FileOutputStream(file);
            workbook.write(fos);
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return file;
    }

    private static <T> Workbook exportData(String sheetName, String title, Class<T> tClass, List<Map<String, Object>> exportValue) {


        List<ExcelExportEntity> entity = new ArrayList<>(16);
        Field[] declaredFields = tClass.getDeclaredFields();

        for (Field field : declaredFields) {
            Excel excel = field.getDeclaredAnnotation(Excel.class);
            if (excel != null) {
                entity.add(new ExcelExportEntity(excel.name(), field.getName()));
            }
        }
        return ExcelExportUtil.exportExcel(new ExportParams(title, sheetName), entity, exportValue);
    }
    //endregion


    /**
     * @param pojoClass Excel对象Class
     * @param dataSet   Excel对象数据List
     */
    public static <T> File export(Class<T> pojoClass, Collection<?> dataSet) {
        return export(DateUtils.dateToString(LocalDateTime.now(), "_yyyy_MM_dd_HH_mm_ss_SSS"), new ExportParams(), pojoClass, dataSet);
    }

    /**
     * @param fileName  文件名
     * @param title     表格标题属性
     * @param sheetName sheet属性
     * @param pojoClass Excel对象Class
     * @param dataSet   Excel对象数据List
     */
    public static File export(String fileName, String title, String sheetName, Class<?> pojoClass, Collection<?> dataSet) {
        ExportParams exportParams = new ExportParams();
        exportParams.setTitle(title);
        exportParams.setSheetName(sheetName);
        exportParams.setStyle(ExcelExportStylerDefaultImpl.class);
        return export(fileName, exportParams, pojoClass, dataSet);
    }

    /**
     * @param fileName     文件名
     * @param exportParams 表格标题属性
     * @param pojoClass    Excel对象Class
     * @param dataSet      Excel对象数据List
     */
    @SuppressWarnings("all")
    public static File export(String fileName, ExportParams exportParams, Class<?> pojoClass, Collection<?> dataSet) {
        File file = null;
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, dataSet);

        if (workbook instanceof HSSFWorkbook) {
            fileName += HSSF;
        } else {
            fileName += XSSF;
        }

        try {
            file = new File(EXPORT_PATH + fileName);
            if (!file.exists()) {
                file.getParentFile().mkdir();
                file.createNewFile();
            }
            OutputStream fos = new FileOutputStream(file);
            workbook.write(fos);
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return file;
    }

    /**
     * @param file   文件
     * @param target Excel对象Class
     * @return Excel对象数据List
     */
    public static <T> List<T> importPoi(File file, T target) {
        ImportParams params = new ImportParams();
        //标题行数
        params.setTitleRows(1);
        //不跳过
        params.setKeyIndex(null);

        return importPoi(file, params, target);
    }

    /**
     * @param file         文件
     * @param target       Excel对象Class
     * @param importParams 导入参数
     * @return Excel对象数据List
     */
    private static <T> List<T> importPoi(File file, ImportParams importParams, T target) {
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file, (Class) target, importParams);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
}
